﻿using System;
using System.Data.OleDb;
using System.IO;
using SBACN.WebSite.Code;
using SBACN.WebSite.Models;

namespace SBACN.WebSite.Admin.MemberService.Education
{
    public partial class ExamUpDownFile : BasePage
    {
        public int ExamID { get; set; }
        public string ExamName { get; set; }

		/// <summary>
		/// 系统字典类型
		/// </summary>
		public string SystemType
		{
			get
			{
				return this.SystemCode;
			}
		}

        protected void Page_Load(object sender, EventArgs e)
        {
			if(!Page.IsPostBack)
			{
				if (!AdminBasePage.CheckCodeRight(AdminBasePage.AdminInfo, SystemType))
				{
					AdminBasePage.RedirectWithMessage(AdminDefaultPage, "您没有访问当前页面的权限,请与系统管理员联系!");
					return;
				}
				
				int id = 0;
				if(int.TryParse(Request.QueryString["id"],out id))
				{
					this.ExamID = id;
					if (ExamID != 0)
					{
						using (var ctx = new SBACNEntities())
						{
							var result = DAL.DALHelper.Instance.GetFirstOrDefaultObject<TB_Examination>(p=>p.ID==ExamID);
							//ctx.TB_Examination.Where(c => (c.ID == this.ExamID)).FirstOrDefault();
							this.ExamName = result.Subject;
						}
						ExportToExcel();
					}
				}
			}
        }

        public void ExportToExcel()
		{
			var result = DAL.DALHelper.Instance.GetList<View_ExamScore>(p => p.Status == 0 && p.ID == ExamID);

			string strTmpFileName = "~/Template/考试报名全名单.xls";
			string strDownloadFileName = "~/Template/Down/" + (result.Count > 0 ? result[0].Subject : string.Empty) + "考试报名全名单" + DateTime.Now.Ticks + ".xls";
			File.Copy(Server.MapPath(strTmpFileName), Server.MapPath(strDownloadFileName), true);

			string strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";

			using (OleDbConnection conn = new OleDbConnection(strExcelConn))
			{
				conn.Open();
				OleDbCommand excmd = new OleDbCommand();
				excmd.Connection = conn;
				foreach (var l in result)
				{
					string insertcmd = " INSERT INTO [Sheet1$] (科目,会员单位,姓名,性别,身份证号码,手机号码,业务成绩,政策成绩,一般成绩,学员类别,补考科目,开票情况,发票抬头,培训联系人姓名,培训联系人手机号,培训联系人电子邮件地址,培训发票领取人姓名,领取人手机号码) VALUES ('" + l.Subject + "','" + l.MemberName + "','" + l.Name + "','" + l.Sex + "','" + l.IDCardNO + "','" + l.CellPhoneNO + "','','','','" + l.StudentType + "','" + l.ReCourse + "','" + l.InvoicesType + "','" + l.InvoicesTitle + "','" + l.Train + "','" + l.Contact + "','" + l.EMail + "','" + l.InvoicesPerson + "','" + l.InvoicesContact + "') ";
					excmd.CommandText = insertcmd;
					excmd.ExecuteNonQuery();
				}
				conn.Close();
			}
			Response.Redirect(strDownloadFileName);
        }
    }
}
